package com.kaikeba.dao.impl;

import com.kaikeba.bean.Courier;
import com.kaikeba.dao.BaseCourierDao;
import com.kaikeba.exception.DuplicateCodeException;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CouriorDaoImpl implements BaseCourierDao {

     static final String SQL_INSERT="INSERT INTO COURIERS(NUMBER,NAME,PHONENUMBER,IDCARDNUM,PASSWORD,SENDEXPRESSNUM,REGISTTIME,LASTLOGINTIME) VALUES(?,?,?,?,?,?,NOW(),?)";
     static final String SQL_UPDATE="UPDATE COURIERS SET NAME=?,PHONENUMBER=?,IDCARDNUM=?,PASSWORD=?,SENDEXPRESSNUM=? WHERE PHONENUMBER=?";
     static final String SQL_DELETE="DELETE FROM COURIERS WHERE PHONENUMBER=?";
     static final String SQL_FIND_ALL="SELECT * FROM COURIERS LIMIT ?,?";
     static final String SQL_GET_EXPRESS_NUM = "SELECT COUNT(NUMBER) FROM EXPRESS WHERE SYSPHONE=?";
    private static final String SQL_CONSOLE = "SELECT COUNT(NUMBER) TOTAL_NUMBER,COUNT(TO_DAYS(REGISTTIME)=TO_DAYS(NOW()) OR NULL) DAY_NUMBER FROM COURIERS";
    private static final String SQL_FIND_COURIER_BY_PHONENUMBER = "SELECT * FROM COURIERS WHERE PHONENUMBER=?";

    /**
     * 获取快递员人数  与  日注册量
     *
     * @return
     */
    @Override
    public List<Map<String, Integer>> console() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Map<String,Integer>> list = new ArrayList();
        Map data = new HashMap();
        try {
            preparedStatement = connection.prepareStatement(SQL_CONSOLE);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                data.put("total_number",resultSet.getString("total_number"));
                data.put("day_number",resultSet.getString("day_number"));
                list.add(data);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }
        return list;
    }

    /**
     * 添加一个新快递员
     *
     * @param courier 快递员对象
     * @return true表示成功
     */
    @Override
    public boolean insert(Courier courier){
        String number = courier.getNumber();
        String name = courier.getName();
        String phoneNumber = courier.getPhoneNumber();
        String idCardNum = courier.getIdCardNum();
        String password = courier.getPassword();
        Timestamp lastLoginTime = courier.getLastLoginTime();

        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            preparedStatement = connection.prepareStatement(SQL_INSERT);
            preparedStatement.setString(1,number);
            preparedStatement.setString(2,name);
            preparedStatement.setString(3,phoneNumber);
            preparedStatement.setString(4,idCardNum);
            preparedStatement.setString(5,password);
            preparedStatement.setInt(6,getSendExpressNumber(phoneNumber));
//            preparedStatement.setTimestamp(7,registTime);
            preparedStatement.setTimestamp(7,lastLoginTime);

            i = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return i>0?true:false;
    }

    /**
     * 根据快递员的电话号码，更新快递员身份信息
     *
     * @param phoneNumber 电话号码
     * @param courier
     * @return
     */
    @Override
    public boolean update(String phoneNumber, Courier courier) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            //UPDATE COURIERS SET NAME=?,PHONENUMBER=?,IDCARDNUM=?,PASSWORD=?,SENDEXPRESSNUM=? WHERE PHONENUMBER=?";
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            preparedStatement.setString(1,courier.getName());
            preparedStatement.setString(2,courier.getPhoneNumber());
            preparedStatement.setString(3,courier.getIdCardNum());
            preparedStatement.setString(4,courier.getPassword());
            // 若更新快递员的电话号码，快递派件数量将会根据新的电话号码重新计数
            preparedStatement.setInt(5,getSendExpressNumber(courier.getPhoneNumber()));
            preparedStatement.setString(6,phoneNumber);
            i = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        System.out.println(i);
        return i>0?true:false;
    }

    /**
     * 根据快递员的电话号码，删除快递员身份信息
     *
     * @param phoneNumber 电话号码
     * @return
     */
    @Override
    public boolean delete(String phoneNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        int i =0;
        try {
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            preparedStatement.setString(1,phoneNumber);
            i = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return i>0?true:false;
    }

    /**
     * 查询所有的快递员信息
        @param limit      是否分页标记 true：分页
      * @param offset     SQL语句的起始索引
     * @param pageNumber 每一页查询的数量
     * @return
     */
    @Override
    public List<Courier> findAll(boolean limit,int offset,int pageNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Courier> data = new ArrayList<>();
        try {
            if(limit==false){
                preparedStatement = connection.prepareStatement("select * from COURIERS");
            }else{
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL);
                preparedStatement.setInt(1,offset);
                preparedStatement.setInt(2,pageNumber);
            }
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                Courier courier = new Courier();
                courier.setNumber(resultSet.getString("number"));
                courier.setName(resultSet.getString("name"));
                courier.setPhoneNumber(resultSet.getString("phoneNumber"));
                courier.setIdCardNum(resultSet.getString("idCardNum"));
                courier.setPassword(resultSet.getString("password"));
                courier.setSendExpressNum(resultSet.getInt("sendExpressNum"));
                courier.setRegistTime(resultSet.getTimestamp("registTime"));
                courier.setLastLoginTime(resultSet.getTimestamp("lastLoginTime"));
                data.add(courier);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return data;
    }

    /**
     * 根据快递员的电话号码，查询出该快递员的派件数量
     *
     * @param phoneNumber
     * @return
     */
    @Override
    public int getSendExpressNumber(String phoneNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int num = 0;
        try {
            preparedStatement = connection.prepareStatement(SQL_GET_EXPRESS_NUM);
            preparedStatement.setString(1,phoneNumber);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                num = resultSet.getInt(1);
            }else{
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return num;
    }

    @Override
    public Courier findCourierByNumber(String phoneNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Courier courier = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_COURIER_BY_PHONENUMBER);
            preparedStatement.setString(1,phoneNumber);
            resultSet = preparedStatement.executeQuery();
            courier = new Courier();
            if (resultSet.next()){
                courier.setNumber(resultSet.getString("number"));
                courier.setName(resultSet.getString("name"));
                courier.setPhoneNumber(resultSet.getString("phoneNumber"));
                courier.setIdCardNum(resultSet.getString("idCardNum"));
                courier.setPassword(resultSet.getString("password"));
                courier.setSendExpressNum(resultSet.getInt("sendExpressNum"));
                courier.setRegistTime(resultSet.getTimestamp("registTime"));
                courier.setLastLoginTime(resultSet.getTimestamp("lastLoginTime"));
                return courier;
            }else{
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return null;
    }
}
